package main.www.wonderfuldata.com;

import main.www.wonderfuldata.PreparedStatement.JDBCUtils;
import main.www.wonderfuldata.bean.User2;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.LinkedList;
import java.util.List;
import java.util.Properties;
import java.util.Scanner;

public class StatementTest {


        // 使用Statement的弊端：需要拼写sql语句，并且存在SQL注入的问题
        public void testLogin() {
            Scanner scan = new Scanner(System.in);

            System.out.print("用户名：");
            String userName ="admin";
            System.out.print("密   码：");
            String password ="123456";

            // SELECT user,password FROM user_table WHERE USER = '1' or ' AND PASSWORD = '='1' or '1' = '1';
            //String sql = "SELECT user,password FROM tudent01 WHERE user = '" + userName + "' AND password = '" + password+ "'";
            String sql = "SELECT name,password FROM User2 WHERE name='"+userName+"'";
            User2 user = get(sql, User2.class);
            if (user != null) {
                System.out.println("登陆成功!"+user);
            } else {
                System.out.println("用户名或密码错误！");
            }
        }


        public void  Test(){
            String sql = "select *from User";
            System.out.println(getAllInstance(User2.class,sql));
        }


        // 使用Statement实现对数据表的查询操作
        public <T> T get(String sql, Class<T> clazz) {
            T t = null;

            Connection conn = null;
            Statement st = null;
            ResultSet rs = null;
            try {
                // 1.加载配置文件
                InputStream is = StatementTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
                Properties pros = new Properties();
                pros.load(is);

                // 2.读取配置信息
                String user = pros.getProperty("user");
                String password = pros.getProperty("password");
                String url = pros.getProperty("url");
                String driverClass = pros.getProperty("driverClass");

                // 3.加载驱动
                Class.forName(driverClass);

                // 4.获取连接
                conn = DriverManager.getConnection(url, user, password);

                st = conn.createStatement();

                rs = st.executeQuery(sql);

                // 获取结果集的元数据
                ResultSetMetaData rsmd = rs.getMetaData();

                // 获取结果集的列数
                int columnCount = rsmd.getColumnCount();


                if (rs.next()) {

                    t = clazz.newInstance();

                    for (int i = 0; i < columnCount; i++) {
                        // //1. 获取列的名称
                        // String columnName = rsmd.getColumnName(i+1);

                        // 1. 获取列的别名
                        String columnName = rsmd.getColumnLabel(i + 1);

                        // 2. 根据列名获取对应数据表中的数据
                        Object columnVal = rs.getObject(columnName);

                        // 3. 将数据表中得到的数据，封装进对象
                        Field field = clazz.getDeclaredField(columnName);
                        field.setAccessible(true);
                        field.set(t, columnVal);
                    }
                    return t;
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                // 关闭资源
                if (rs != null) {
                    try {
                        rs.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if (st != null) {
                    try {
                        st.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }

                if (conn != null) {
                    try {
                        conn.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }

            return null;
        }







        //针对不同的表的
    public <T> T getInstance(Class<T> clazz, String sql, Object... args) {



        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            // 1.获取数据库连接
            conn = JDBCUtils.getConnection();

            // 2.预编译sql语句，得到PreparedStatement对象
            ps = conn.prepareStatement(sql);

            // 3.填充占位符
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }

            // 4.执行executeQuery(),得到结果集：ResultSet
            rs = ps.executeQuery();

            // 5.得到结果集的元数据：ResultSetMetaData
            ResultSetMetaData rsmd = rs.getMetaData();

            // 6.1通过ResultSetMetaData得到columnCount,columnLabel；通过ResultSet得到列值
            int columnCount = rsmd.getColumnCount();

            if (rs.next()) {
                T t = clazz.newInstance();
                for (int i = 0; i < columnCount; i++) {// 遍历每一个列

                    // 获取列值
                    Object columnVal = rs.getObject(i + 1);
                    // 获取列的别名:列的别名，使用类的属性名充当
                    String columnLabel = rsmd.getColumnLabel(i + 1);
                    // 6.2使用反射，给对象的相应属性赋值
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columnVal);

                }

                return t;

            }
        } catch (Exception e) {

            e.printStackTrace();
        } finally {
            // 7.关闭资源
            JDBCUtils.closeResource(conn, ps, rs);
        }

        return null;

    }




    //查询多个数据
    public  <T> List<T> getAllInstance(Class<T> clazz, String sql, Object... args) {


        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            // 1.获取数据库连接
            conn = JDBCUtils.getConnection();

            // 2.预编译sql语句，得到PreparedStatement对象
            ps = conn.prepareStatement(sql);

            // 3.填充占位符
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }

            // 4.执行executeQuery(),得到结果集：ResultSet
            rs = ps.executeQuery();

            // 5.得到结果集的元数据：ResultSetMetaData
            ResultSetMetaData rsmd = rs.getMetaData();
            //

            // 6.1通过ResultSetMetaData得到columnCount,columnLabel；通过ResultSet得到列值


            List<T> list = new LinkedList<T>();
            int columnCount = rsmd.getColumnCount();
            while (rs.next()) {

                T t = clazz.newInstance();
                for (int i = 0; i < columnCount; i++) {// 遍历每一个列

                    // 获取列值
                    Object columnVal = rs.getObject(i + 1);
                    // 获取列的别名:列的别名，使用类的属性名充当
                    String columnLabel = rsmd.getColumnLabel(i + 1);
                    // 6.2使用反射，给对象的相应属性赋值
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columnVal);

                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {

            e.printStackTrace();
        } finally {
            // 7.关闭资源
            JDBCUtils.closeResource(conn, ps, rs);
        }

        return null;

    }


}

